package booroo.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
// import java.sql.*;
import booroo.model.J20_BookDBVO;
import booroo.interfaces.J20_bookDBService;

public class J20_BookDBDAO implements J20_bookDBService{
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER"; 
	
	private static final String insertSQL = "insert into book(id,title,publisher,author) values(id.nextval,?,?,?)"; 
	private static final String selectSQL = "select * from book order by id asc"; 
	private static final String select1SQL = "select * from book where id = ? order by id asc"; 
	private static final String select2SQL = "select id,title from book order by id asc"; 
	private static final String updateSQL = "update book set title = ?,publisher = ?,author = ?,regdate=sysdate where id = ?"; 
	private static final String deleteSQL = "delete from book where id = ?";
	@Override
	public int insertBookDB(J20_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
						
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(insertSQL);
			ps.setString(1,bvo.getTitle());
			ps.setString(2,bvo.getPublisher());
			ps.setString(3,bvo.getAuthor());
			result = ps.executeUpdate();
						
		}catch(Exception ex){ex.printStackTrace();}finally{
			if(ps != null)try {ps.close();} catch (Exception e){}
			if(conn != null)try{conn.close();}catch(Exception e){}
		}
		return result;
		
	}
	@Override
	public String selectBookDB() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
				
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			while(rs.next()){
				for(int i = 1;i <= 5;++i){
					result += rs.getString(i) + " ";
					}
				result += "\n";
			}
		}catch(Exception ex){ex.printStackTrace();}finally{
			if(ps != null)try {ps.close();} catch (Exception e){}
			if(rs != null)try{rs.close();}catch(Exception e){}
			if(conn != null)try{conn.close();}catch(Exception e){}
			
		}
		return result;
	}
	@Override
	public String selectBookDB2(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
				
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(select1SQL);
			ps.setString(1,id);
			rs = ps.executeQuery();
			
			while(rs.next()){
				for(int i = 1;i <= 5;++i){
					result += rs.getString(i) + " ";
					}
				result += "\n";
			}
		}catch(Exception ex){ex.printStackTrace();}finally{
			if(ps != null)try {ps.close();} catch (Exception e){}
			if(rs != null)try{rs.close();}catch(Exception e){}
			if(conn != null)try{conn.close();}catch(Exception e){}
		}
			return result;
	}
	public String selectBookDB3() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(select2SQL);
			rs = ps.executeQuery();
			
			while(rs.next()){
				for(int i = 1;i <= 2;++i){
					result += rs.getString(i) + " ";
				}
				result += "\n";
			}
		}catch(Exception ex){ex.printStackTrace();}finally{
			if(ps != null)try {ps.close();} catch (Exception e){}
			if(rs != null)try{rs.close();}catch(Exception e){}
			if(conn != null)try{conn.close();}catch(Exception e){}
		}
		return result;
	}
	@Override
	public int updateBookDB(J20_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
		
		try{
		Class.forName(DRV);
		conn = DriverManager.getConnection(URL,USR,PWD);
		ps = conn.prepareStatement(updateSQL);
		
		ps.setString(1,bvo.getTitle());
		ps.setString(2,bvo.getPublisher());
		ps.setString(3,bvo.getAuthor());
		ps.setString(4,bvo.getId());
		
		result = ps.executeUpdate();
				
		}catch(Exception ex){ex.printStackTrace();}finally{
			if(ps != null)try {ps.close();} catch (Exception e){}
			if(conn != null)try{conn.close();}catch(Exception e){}
		}
		return result;
	}
	@Override
	public int deleteBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
		
		try{
		Class.forName(DRV);
		conn = DriverManager.getConnection(URL,USR,PWD);
		ps = conn.prepareStatement(deleteSQL);
		ps.setString(1, id);
		result = ps.executeUpdate();
		
		}catch(Exception ex){ex.printStackTrace();}finally{
			if(ps != null)try {ps.close();} catch (Exception e){}
			if(conn != null)try{conn.close();}catch(Exception e){}
		}
		return result;
	}
		
}
